/**
 * Qiangungun.com Inc.
 * Copyright (c) 2015-2015 All Rights Reserved.
 */
package com.qiangungun.report.biz.share;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Vector;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.springframework.util.Assert;

import com.qiangungun.core.util.CoreDateUtils;
import com.qiangungun.report.common.model.config.DataSetParam;
import com.qiangungun.report.service.enums.DataTypeCode;

/**
 * 查询语句Statement转换类
 * @author hangdahong
 * @version $Id: DataBaseAssist.java, v 0.1 2015年3月4日 下午8:17:45 hangdahong Exp $
 */
public class DataBaseAssist {
    
    /**
     * 以prepareStatement形式查询数据<br>
     * 将参数 ":PARAM"转换成 "?"<br>
     * 例如：原始的 sql语句：select * from table where id=:id <br>
     *       转换后sql语句：select * from table where id=?
     *  
     * 
     * @param preSql
     * @param params
     * @param connect
     * @return
     * @throws SQLException 
     */
    public static ResultSet query(String preSql,Map<String,DataSetParam> paramMap,Connection connect) throws SQLException{
        
        String realSql=preSql;
        
        Pattern p=Pattern.compile(":[a-zA-Z_0-9]+"); 
        Matcher m=p.matcher(preSql);
        
        List<String> paramList=new LinkedList<String>();
        String param=null;
        while(m.find()) { 
            param = m.group();
            realSql=realSql.replaceAll(param, "\\?");
            param=param.replaceAll(":", "");
            paramList.add(param);
        } 
        PreparedStatement statement= connect.prepareStatement(realSql);
        
        DataSetParam dataSetParam=null;
        String dateType=null;
        String dateValue=null;
        int idx=0;
        for(int i =0 ;i<paramList.size();i++){
            idx=i+1;
            param=paramList.get(i);
            dataSetParam=paramMap.get(param);
            Assert.notNull(dataSetParam);
            dateType=dataSetParam.getParamType();
            dateValue=dataSetParam.getParamValue();
            if(DataTypeCode.ARRAY_CHAR.name().equals(dateType)){
                Vector<String> v=new Vector<String>();
                for(String element:dateValue.split(",")){
                    v.add(element);
                }
                statement.setObject(idx, v);
            }else if(DataTypeCode.ARRAY_LONG.name().equals(dateType)){
                Vector<Long> v=new Vector<Long>();
                for(String element:dateValue.split(",")){
                    v.add(new Long(element));
                }
                statement.setObject(idx, v);
            }else if(DataTypeCode.ARRAY_INT.name().equals(dateType)){
                Vector<Integer> v=new Vector<Integer>();
                for(String element:dateValue.split(",")){
                    v.add(new Integer(element));
                }
                statement.setObject(idx, v);
            }else if(DataTypeCode.ARRAY_SHORT.name().equals(dateType)){
                Vector<Short> v=new Vector<Short>();
                for(String element:dateValue.split(",")){
                    v.add(new Short(element));
                }
                statement.setObject(idx, v);
            }else if(DataTypeCode.CHAR.name().equals(dateType)){
                
                statement.setString(idx, dateValue);
            }else if(DataTypeCode.LONG.name().equals(dateType)){
                statement.setLong(idx, Long.parseLong(dateValue));
            }else if(DataTypeCode.INT.name().equals(dateType)){
                statement.setInt(idx, Integer.parseInt(dateValue));
            }else if(DataTypeCode.SHORT.name().equals(dateType)){
                statement.setShort(idx, Short.parseShort(dateValue));
            }else if(DataTypeCode.DATETIME.name().equals(dateType)){
                Date date=new Date(CoreDateUtils.parseToDate(dateValue, CoreDateUtils.STANDARD_FORMAT).getTime());
                statement.setDate(idx, date);
            }else if(DataTypeCode.DATE.name().equals(dateType)){
                Date date=new Date(CoreDateUtils.parseToDate(dateValue, CoreDateUtils.STANDARD_FORMAT).getTime());
                date=new Date(CoreDateUtils.yyyymmdd(CoreDateUtils.yyyymmdd(date)).getTime());
                statement.setDate(idx, date);
            }else{
                throw new IllegalArgumentException("不支持的数据类型");
            }
            
        }
        
       return  statement.executeQuery();
        
    }
    
    
    
    public static void main(String [] args){
        Pattern p=Pattern.compile(":[a-zA-Z_0-9]+"); 
        String sttr="我的QQ是:456A4_56 我的电话是:A53_22a4 我的邮箱是:aaa123@aaa.com";
        Matcher m=p.matcher(sttr); 
        while(m.find()) { 
            
            String param=m.group();
           String aa= sttr.replaceAll(param, "\\?");
           System.out.println(aa);
            
       } 
    }

}
